<?php
/**
 * 
 * @todo description of this class
 * 
 * @author Alton Crossley <crossleyframework@nogahidebootstrap.com>
 * @package Crossley Framework
 *  
 * @copyright Copyright (c) 2003-2009, Nogaide BootStrap INC. All rights reserved.
 * @license BSD http://opensource.org/licenses/bsd-license.php
 * @version $Id:$
 * 
 */
class X_Data_Adapter_Mysql extends X_Data_Adapter_Pdo
{
	/**
	 * 'mysql:host=localhost;dbname=universa_bozemanbaseball'
	 */
	/**
	 * standard constructor
	 * 
	 * @param string $sHost 
	 * @param string $sDbName 
	 * @param string $sUser 
	 * @param string $sPass 
	 * @param array $aOptions 
	 */
	function __construct($sHost, $sDbName, $sUser = null, $sPass = null,Array $aOptions = array(PDO::ATTR_PERSISTENT => true))
    {
    	if (in_array('mysql', PDO::getAvailableDrivers()))
    	{
    		parent::__construct("mysql:host={$sHost};dbname={$sDbName}", $sUser, $sPass, $aOptions);
    	}
    	else
    	{
    		throw new X_Data_Exception('mysql pdo driver is not available, you need to install or enable it');
    	}
    }
    /**
     * get an instance of the current class
     *
     * @param string $sConnectionString
     * @param string $sUser
     * @param string $sPass
     * @param array $aOptions
     * @return X_Data_Adapter_Mysql
     */
    static public function getInstance($sConnectionString, $sUser = null, $sPass = null, Array $aOptions = array(PDO::ATTR_PERSISTENT => true))
    {
        if (! self::$_oInstance)
        {
            $sClass = __CLASS__;
            self::$_oInstance = new $sClass($sConnectionString, $sUser, $sPass, $aOptions);
        }
        return self::$_oInstance;
    }
    /**
     * table descriptions
     *
     * @var array of X_Data_Meta_Table
     */
    protected static $aTableDetails = array();
    /**
     * fetch a table description array
     *
     * @param mysqli $oConn
     * @param string $sTable
     * @param string $sDBName
     * @param bool $bForceUpdate
     * @return X_Data_Meta_Table
     */
    public function describeTable($sTable, $sDBName = null, $bForceUpdate = false)
    {
        // fully qualify the name if given
        $sTableName = (! empty($sDBName)) ? $sDBName . '.' . $sTable : $sTable;
        // if we alread did this table this time arround, just return it
        if (array_key_exists($sTableName, self::$aTableDetails) && ! $bForceUpdate)
        {
            return self::$aTableDetails[$sTableName];
        }
        try
        {
            // @todo make sure this works!!!
            $oResult = $this->query(sprintf('SHOW CREATE TABLE %s', $sTableName));
        }
        catch (Exception $e)
        {
            self::$sError = $e->getMessage();
            return array();
        }
        if ($oResult)
        {
            $aResult = $oResult->fetchAll();
            //print X_Debug::formatHtml("<pre>".$aResult[0]['Create Table']."</pre>", 'Create');
            // this is where the magic happens
            $aStructure = self::getObjectFromCreate($aResult[0]['Create Table']);
            //print X_Debug::formatHtml($aStructure, '$aStructure');
            self::$aTableDetails[$sTableName] = $aResult;
            return $aStructure;
        }
        else
        {
            trigger_error("unable to get $sType {$sDBName}.{$sTable}", E_USER_ERROR);
            return array();
        }
    }
    /**
     * parse a create statement into an object
     *
     * @param string $sCreate
     * @return X_Data_Meta_Table
     */
    static public function getObjectFromCreate($sCreate)
    {
        $aReturn = array();
        preg_match('|CREATE TABLE `(\w*)`|', $sCreate, $aMatches);
        $oTable = X_Data_Meta_Table::fromArray(array('table_name' => $aMatches[1] , 'table_abbreviation' => self::abbreviateTableName($aMatches[1])));
        preg_match_all('|\(([\w\W]*)\) ENGINE|', $sCreate, $aMatches);
        $sInnerDef = $aMatches[1][0];
        $iMatches = preg_match_all('|\n  `(\w*)`\s?(\w*)\(?(\d*?)\)?([\w\s\']*),|', $sInnerDef, $aMatches);
        $aColumns = array();
        for ($i = 0; $i <= $iMatches - 1; $i ++)
        {
            $sExtra = str_ireplace('NOT NULL', '', str_ireplace('default NULL', '', $aMatches[4][$i], $bDefaultNull), $bNotNull);
            $sExtra = str_ireplace('AUTO_INCREMENT', '', $sExtra, $bAutoIncrement);
            $sExtra = str_ireplace('UNSIGNED', '', $sExtra, $bUnsigned);
            $oTable->addColumn(X_Data_Meta_Column::fromArray(array('name' => $aMatches[1][$i] , 'type' => $aMatches[2][$i] , 'bind_type' => self::getBindTypeCode($aMatches[2][$i]) , 'length' => $aMatches[3][$i] , 'extra' => $sExtra , 'auto_increment' => (bool) $bAutoIncrement , 'unsigned' => (bool) $bUnsigned , 'nullable' => ! $bNotNull , 'default_null' => ($bDefaultNull))));
        }
        $iMatches = preg_match_all('|CONSTRAINT `(\w*)` FOREIGN KEY \(`(\w*)`\) REFERENCES `(\w*)` \(`(\w*)`\)(.*?),|', $sInnerDef, $aMatches);
        $aFKs = array();
        for ($i = 0; $i <= $iMatches - 1; $i ++)
        {
            $oForienKey = X_Data_Meta_Forienkey::fromArray(array('name' => $aMatches[1][$i] , 'column' => $aMatches[2][$i] , 'to_table' => $aMatches[3][$i] , 'to_abbreviated_name' => self::abbreviateTableName($aMatches[3][$i]) , 'to_column' => $aMatches[4][$i]));
            $oTable->addForienKey($oForienKey);
            if (preg_match_all('|ON UPDATE (\w*)|', $aMatches[5][$i], $aOnUpdate))
            {
                $oForienKey->on_update = self::decodeAction($aOnUpdate[1][0]);
            }
            else
            {
                $oForienKey->on_update = 'RESTRICT';
            }
            if (preg_match_all('|ON DELETE (\w*)|', $aMatches[5][$i], $aOnDelete))
            {
                $oForienKey->on_delete = self::decodeAction($aOnDelete[1][0]);
            }
            else
            {
                $oForienKey->on_delete = 'RESTRICT';
            }
        }
        // get PK
        if (preg_match_all('/primary\skey[\s\w]*\(\W(\w*)\W\)/i', $sInnerDef, $aMatches))
        {
            $oTable->pk = $aMatches[1][0];
        }
        preg_match('/ENGINE=(\S*)/i', $sCreate, $aMatches);
        if (! empty($aMatches))
        {
            $oTable->engine = $aMatches[1];
        }
        preg_match('/AUTO_INCREMENT=(\d*)/i', $sCreate, $aMatches);
        if (! empty($aMatches))
        {
            $oTable->auto_increment = $aMatches[1];
        }
        preg_match('/DEFAULT\sCHARSET=(\S*)/i', $sCreate, $aMatches);
        if (! empty($aMatches))
        {
            $oTable->default_charset = $aMatches[1];
        }
        // X_Debug::formatHtml($aMatches);
        $oTable->columns = $aColumns;
        $oTable->fks = $aFKs;
        return $oTable;
    }
    /**
     * decode single word actions for OnDelete and OnUpdate fk events
     *
     * @param string $sAction
     * @return string
     */
    static protected function decodeAction($sAction)
    {
        switch (strtoupper($sAction))
        {
            case 'CASCADE':
                return 'CASCADE';
                break;
            case 'SET':
                return 'SET NULL';
                break;
            case 'NO':
                return 'NO ACTION';
                break;
            default:
                return 'RESTRICT';
                break;
        }
    }
    /**
     * translate simple type into bind type
     *
     * @param string $sType
     * @return char
     */
    static public function getBindTypeCode($sType)
    {
        switch (true)
        {
            case ($sType == 'int'):
                return 'i';
                break;
            case (strpos($sType, "dec") !== false):
                return 'd';
                break;
            case (strpos($sType, "blob") !== false):
                return 'b';
                break;
            default:
                return 's';
                break;
        }
    }
}